# coding=utf-8
import xlrd
import pymysql
import os

MYSQL_HOST = '127.0.0.1'
MYSQL_PORT = 3306
MYSQL_USER = 'root'
MYSQL_PWD = 'root'
MYSQL_DB = 'home_db'

db = pymysql.connect(host=MYSQL_HOST, port=MYSQL_PORT, user=MYSQL_USER, passwd=MYSQL_PWD, db=MYSQL_DB)

def readExcel():
    path = r"C:/Users/zhao/Desktop/社保缴费信息"
    dirs = os.listdir(path)
    dicData = {}
    for dir in dirs:
        filename = dir
        filepath = path + '/' + filename

        dicData['name'] = filename.split('-')[1].split('.')[0]

        data = xlrd.open_workbook(filepath)

        yanglao = data.sheet_by_name('养老保险')
        yiliao = data.sheet_by_name('医疗保险')
        gongshang = data.sheet_by_name('工伤保险')
        shiye = data.sheet_by_name('失业保险')
        shengyu = data.sheet_by_name('生育保险')

        for nrows_one in range(1, yanglao.nrows):

            if yanglao.cell_value(nrows_one, 2) == '113804':
                dicData['fdate'] = yanglao.cell_value(nrows_one, 1)
                dicData['fyanglaoje'] = float(yanglao.cell_value(nrows_one, 7))

                if yiliao.nrows - 3 >= nrows_one:
                    if yiliao.cell_value(nrows_one, 2) == '113804':
                        dicData['fyiliaoje'] = float(yiliao.cell_value(nrows_one, 8))
                    else:
                        dicData['fyiliaoje'] = 0
                else:
                    dicData['fyiliaoje'] = 0

                if gongshang.nrows - 3 >= nrows_one:
                    if gongshang.cell_value(nrows_one, 2) == '113804':
                        dicData['fgongshangje'] = float(gongshang.cell_value(nrows_one, 7))
                    else:
                        dicData['fgongshangje'] = 0
                else:
                    dicData['fgongshangje'] = 0

                if shiye.nrows - 3 >= nrows_one:
                    if shiye.cell_value(nrows_one, 2) == '113804':
                        dicData['fshiyeje'] = float(shiye.cell_value(nrows_one, 7))
                    else:
                        dicData['fshiyeje'] = 0
                else:
                    dicData['fshiyeje'] = 0

                if shengyu.nrows - 3 >= nrows_one:
                    if shengyu.cell_value(nrows_one, 2) == '113804':
                        dicData['fshengyuje'] = float(shengyu.cell_value(nrows_one, 7))
                    else:
                        dicData['fshengyuje'] = 0
                else:
                    dicData['fshengyuje'] = 0

                write2Mysql(dicData)
            else:
                continue


def write2Mysql(dictData):
    cur = db.cursor()

    selectSQL = "select exists(select 1 from t_ygcbxx where name=%(name)s and fdate=%(fdate)s)"

    value = {
        'name': dictData['name'],
        'fdate': dictData['fdate']
    }
    cur.execute(selectSQL, value)
    cnt = cur.fetchall()[0]

    if cnt[0] == 1:
        print('【' + dictData['name'] + '】的【' + dictData['fdate'] + '】社保数据，已经存在！')
    else:
        sql = "insert into t_ygcbxx(name,fdate,fyanglaoje,fyiliaoje,fgongshangje,fshiyeje,fshengyuje) " \
              "values(%(name)s,%(fdate)s,%(fyanglaoje)s,%(fyiliaoje)s,%(fgongshangje)s," \
              "%(fshiyeje)s,%(fshengyuje)s)"

        cur.execute(sql, dictData)
        db.commit()
